import sqlite3
import os
import epub

dir = "books/"

index = 0

insertSql = "insert into Book values(?, ?, ?, ?)"
searchAllSql = "select * from Book"
searchByIdSql = "select * from Book where id = ?"

def insertFromEpub(cursor, filename):


    with epub.open_epub(dir + filename) as ep:

        global index
            
        ncxbytes = ep.read_item(ep.opf.manifest['ncx'])
        ncx = epub.ncx.parse_toc(ncxbytes)
        src = dir+filename
        name = ncx.title
        authors = '#'.join(ncx.authors)
        cursor.execute(insertSql, (index, dir + filename, name, authors))
        index += 1


def initdb():

    conn = sqlite3.connect("books.db")
    cursor = conn.cursor()
    cursor.execute("drop table Book")
    cursor.execute("create table Book("\
            "id int primarykey, "\
            "src varchar(30), "\
            "name varchar(50), "\
            "authors varchar(256)"\
        ")")

    global index
    index = 0

    for filename in os.listdir(dir):
        insertFromEpub(cursor, filename)
    
    cursor.close()
    conn.commit()
    conn.close()

def searchAll(cursor):

    cursor.execute(searchAllSql)
    res= cursor.fetchall()
    
    return res
    

def searchById(cursor, id):

    cursor.execute(searchByIdSql, (1, ))
    res = cursor.fetchall()
    
    return res

#conn.row_factory = dict_factory
def dict_factory(cursor, row):
    
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]

    return d